package com.linln.modules.system.repository;

import com.linln.modules.system.domain.Order;
import com.linln.modules.system.domain.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import org.springframework.data.domain.Pageable;
import java.math.BigDecimal;
import java.util.List;

public interface OrderRepository extends BaseRepository<Order, Long> , JpaSpecificationExecutor<Order> {

    /**
     * 根据年份、公司ID、订单状态和订单类型查询总金额
     *
     * @param year 年份，用于筛选订单
     * @param companyId 公司ID，用于筛选订单
     * @param orderType 订单类型，用于筛选订单
     * @return 符合条件的订单总金额
     */
    @Query("SELECT SUM(o.total) AS totalAmount " +
            "FROM Order o " +
            "WHERE YEAR(o.createTime) = :year AND o.companyId = :companyId AND o.orderStatus in(5,6) AND o.type = :orderType AND o.admissionStatus=1")
    BigDecimal findTotalAmountByYear(
            Integer year,
            Long companyId,
            Integer orderType
    );

    @Query("SELECT count(id) as num,projectId,numberLimit,porterId FROM Order o where :timeStart < o.timeEndTemp  and :timeEndTemp > o.timeStart " +
            "and o.companyId = :companyId and o.type = 1 and o.payStatus = 1 and o.orderStatus in (0,1,2,3,4) GROUP BY o.porterId")
    List<Object> checkOrderList(
            String timeStart,
            Long companyId,
            String timeEndTemp
    );

    @Query("SELECT o.porterId, COUNT(o.id) AS completedOrderCount " +
            "FROM Order o " +
            "WHERE (:companyId IS NULL OR o.companyId = :companyId) " +
            "AND o.orderStatus in(5,6) " +
            "GROUP BY o.porterId " )
    List<Object> findCompletedOrdersByCompanyIdAndGroupByPorterId(
            @Param("companyId") Long companyId,
            Pageable pageable
    );

    @Query("SELECT SUM(o.total) AS totalAmount, DATE_FORMAT(o.createTime, '%c月') AS month " +
            "FROM Order o " +
            "WHERE o.createTime BETWEEN CAST(CONCAT(YEAR(CURRENT_DATE()), '-01-01') AS date) " +
            "AND CAST(CONCAT(YEAR(CURRENT_DATE()), '-12-31') AS date) " +
            "AND (:companyId IS NULL OR o.companyId = :companyId) AND o.orderStatus = :orderStatus  " +
            "GROUP BY DATE_FORMAT(o.createTime, '%Y-%m')")
    List<Object[]> findTotalAmountByMonth(
            @Param("companyId") Long companyId,
            @Param("orderStatus") Integer orderStatus
    );

    @Query("SELECT COUNT(o.id) AS num, DATE_FORMAT(o.createTime, '%c月') AS month " +
            "FROM Order o " +
            "WHERE o.createTime BETWEEN CAST(CONCAT(YEAR(CURRENT_DATE()), '-01-01') AS date) " +
            "AND CAST(CONCAT(YEAR(CURRENT_DATE()), '-12-31') AS date) " +
            "AND (:companyId IS NULL OR o.companyId = :companyId) AND o.orderStatus in(5,6)  " +
            "GROUP BY DATE_FORMAT(o.createTime, '%Y-%m')")
    List<Object[]> findNumByMonth(
            @Param("companyId") Long companyId
    );



    @Query("SELECT o.type, COUNT(o.id) AS completedOrderCount " +
            "FROM Order o " +
            "WHERE o.createTime BETWEEN CAST(CONCAT(YEAR(CURRENT_DATE()), '-01-01') AS date) " +
            "AND CAST(CONCAT(YEAR(CURRENT_DATE()), '-12-31') AS date) " +
            "AND (:companyId IS NULL OR o.companyId = :companyId) " +
            "AND o.orderStatus in(5,6) " +
            "GROUP BY o.type " )
    List<Object[]> findCompletedOrdersByCompanyIdAndGroupByType(
            @Param("companyId") Long companyId
    );

    @Query(value = "SELECT * FROM `pcs_order` p where p.status =1 and p.company_id = :companyId and p.pay_status=1 and order_status>0 and \n" +
            "case p.is_need_sign_contract\n" +
            "when 1 then p.type in (1,2,3)\n" +
            "when 0 then p.type = 1\n" +
            "end order by id desc",nativeQuery = true)
    Page<Order> selectAllCompanyIdAndOrderStatus(Long companyId, Pageable pageable);

    @Query(value = "SELECT * FROM `pcs_order` p where p.status =1 and p.company_id = :companyId and p.pay_status=1 and order_status=1 and \n" +
            "case p.is_need_sign_contract\n" +
            "when 1 then p.type in (1,2,3)\n" +
            "when 0 then p.type = 1\n" +
            "end order by id desc",nativeQuery = true)
    Page<Order> selectWaitCompanyIdAndOrderStatus(Long companyId, Pageable pageable);

    @Query(value = "SELECT * FROM `pcs_order` p where p.status =1 and p.company_id = :companyId and p.pay_status=1 and order_status>1 and \n" +
            "case p.is_need_sign_contract\n" +
            "when 1 then p.type in (1,2,3)\n" +
            "when 0 then p.type = 1\n" +
            "end order by id desc",nativeQuery = true)
    Page<Order> selectAftCompanyIdAndOrderStatus(Long companyId, Pageable pageable);


    @Query("SELECT o.companyId, COUNT(o.id) AS completedOrderCount " +
            "FROM Order o " +
            "WHERE o.orderStatus in(5,6) " +
            "GROUP BY o.companyId " )
    List<Object> findCompletedOrdersByCompanyIdAndGroupByCompanyId(
            Pageable pageable
    );


    @Query(value = "SELECT * FROM pcs_order where start_day<:nowDay and order_status=0 and type in (2,3)",nativeQuery = true)
    List<Order> selectOrderList(String nowDay);
}
